package com.ukefu.webim.service.repository;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.alibaba.druid.pool.DruidDataSource;
import com.ukefu.util.UKTools;
import com.ukefu.util.bi.CubeReportData;
import com.ukefu.util.bi.model.FirstTitle;
import com.ukefu.util.bi.model.Level;
import com.ukefu.util.bi.model.ValueData;
import com.ukefu.util.es.SearchTools;
import com.ukefu.webim.web.model.ColumnProperties;
import com.ukefu.webim.web.model.ColumnPropertiesStrReplace;

import freemarker.template.TemplateException;

public class SqlCubeService {
	
	private DruidDataSource druidDataSource;
	
	public SqlCubeService(DruidDataSource druidDataSource) throws IOException, TemplateException {
		this.druidDataSource = druidDataSource ;
	}
	
	public CubeReportData execute(String sql,List<ColumnProperties> cols) throws Exception{
		Connection connection = null ;
		CubeReportData cubeReportData = new CubeReportData();
		try{
			connection = druidDataSource.getConnection() ;
			PreparedStatement ps = connection.prepareStatement(sql);
		    ResultSet resultSet = ps.executeQuery();
		    
			List<List<ValueData>> valuedatalist =  new ArrayList<List<ValueData>>();
			Level row = new Level("root", "row" , null , 0);
			Level col = new Level("root", "col" , null , 0);
			cubeReportData.setRow(row);
			cubeReportData.setCol(col);
			row.setChilderen(new ArrayList<Level>());
			row.setTitle(new ArrayList<List<Level>>());
			row.getTitle().add(new ArrayList<Level>()) ;
			List<Level> titlesList = new ArrayList<>();
			
			List<FirstTitle> firstTitle = new ArrayList<FirstTitle>();
			for(ColumnProperties colp : cols){
				firstTitle.add(new FirstTitle(colp.getTitle(), 1, false));
			}
			row.setFirstTitle(firstTitle);
			
			int num = 0;
			while (resultSet.next()){
				num ++;
				List<ValueData> vdlist = new ArrayList<>();
		    	if (cols != null && cols.size() > 0) {
		    		
		    		Level title = new Level(resultSet.getString(cols.get(0).getColname()), "row" , null , 1);
					title.setParent(row);
					row.getTitle().get(0).add(title);
		    		
					SimpleDateFormat dateFormate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
					for(ColumnProperties colp : cols){
						String value = resultSet.getString(colp.getColname());
						if (!StringUtils.isBlank(value) && value.contains(".") && SearchTools.isDate(value, dateFormate)) {
							//处理日期字符
							value = value.substring(0, value.indexOf("."));
						}
						String foramatValue = value;
						if("order".equals(colp.getType())) {
							foramatValue = Integer.toString(num);
							value = Integer.toString(num);
						}
						if("0".equals(colp.getFormat()) && !StringUtils.isBlank(foramatValue)) {
							foramatValue = UKTools.getDuration(0, Long.parseLong(foramatValue)) ;
						}else if(!StringUtils.isBlank(colp.getFormat())){
							foramatValue = new DecimalFormat(colp.getFormat()).format(Double.parseDouble(foramatValue)) ;
						}
						List<ColumnPropertiesStrReplace> cpsrList = colp.getColumnPropertiesStrReplace();
						if (cpsrList!= null && cpsrList.size() > 0) {
							for(ColumnPropertiesStrReplace cpsr : cpsrList){
								if ((!StringUtils.isBlank(cpsr.getWord()) && cpsr.getWord().equals(foramatValue)) 
										|| (StringUtils.isBlank(cpsr.getWord()) && StringUtils.isBlank(foramatValue) )) {
									foramatValue = cpsr.getRepword() ;
								}
							}
						}
						vdlist.add(processValueData(new ValueData(colp.getColname(), value, foramatValue, null),title));
					}
				}
		    	valuedatalist.add(vdlist);
	    	}
			cubeReportData.setData(valuedatalist);
			
			cubeReportData.getCol().setFirstTitle(firstTitle);
			cubeReportData.getCol().setTitle(new ArrayList<List<Level>>());
			cubeReportData.getCol().getTitle().add(titlesList);
			
			cubeReportData.getRow().setTitle(new ArrayList<List<Level>>()) ;
			processTitle(cubeReportData.getRow() , cubeReportData.getRow());
			
		}catch(Exception ex){ 
			ex.printStackTrace();
			throw ex;
		}finally{
			if(connection!=null){
				connection.close();
			}
		}
		return cubeReportData ;
	}
	
	public ValueData processValueData(ValueData valueData , Level row){
		if (valueData != null) {
			valueData.setRow(row);
		}
		return valueData;
	}
	
	private int getDepth(Level level){
		int depth = 0 ;
		while((level = level.getParent())!=null){
			depth++ ;
		}
		return depth - 1 ;
	}
	
	/*
	 * 
	 */
	private void processTitle(Level level , Level root){
		for(int i=0 ; level.getChilderen()!=null && i<level.getChilderen().size() ; i++){
			Level child = level.getChilderen().get(i) ;
			int depth = getDepth(child) ;
			if(depth>=0){
				if(root.getTitle().size()<=depth){
					root.getTitle().add(new ArrayList<Level>()) ;
				}
				Level tempLevel = new Level(child.getName() , child.getNameValue() , child.getLeveltype() , child.getRowspan() , child.getColspan() , child.getValueData() , child.isTotal() , child.isFirst()) ;
				tempLevel.setParent(child) ;
				root.getTitle().get(depth).add(tempLevel)  ;
			}
			if((child.getNameValue().equals("UCKEFU_TOTAL") && root.getFirstTitle()!=null && (depth+1) < root.getFirstTitle().size()) || (child.getChilderen()==null && root.getFirstTitle()!=null && (depth+1) < root.getFirstTitle().size())){
				child.setChilderen(new ArrayList<Level>()) ;
				child.setColspan(root.getFirstTitle().size() - depth);
				if(root.getTitle()!=null && root.getTitle().size()>depth){
					for(Level title : root.getTitle().get(depth)){
						if(title.getName().equals(child.getName())){
							title.setColspan(child.getColspan()) ;
						}
					}
				}
				Level tempLevel = new Level("TOTAL_TEMP" , "UCKEFU_TOTAL" , child.getLeveltype() ,child.getRowspan() ,child.getColspan(), child.getValueData() , child.isTotal() , child.isFirst() , child.getDepth()+1) ;
				tempLevel.setParent(child) ;
				child.getChilderen().add(tempLevel) ;
				child.setValue(null) ;
			}
			processTitle(child , root);
		}
	}
}
